<?php
function already_entered($historical_quantities, $record){
    for($i = 0; $i < count($historical_quantities)-1; $i++){
        if($historical_quantities[$i]['measure_code'] == $record['measure_code'] &&
                $historical_quantities[$i]['date'] == $record['date']){
            return $i;
        }
    }
    return false;
}

function process_subdivision_sheet(PHPExcel_Worksheet $sheet) {
    global $subdivisions;
    for ($row = 2; ($subdivisions[$row - 2]['code'] = $sheet->getCellByColumnAndRow(0, $row)->getValue()) != ''; $row++)
        $subdivisions[$row - 2]['title'] = $sheet->getCellByColumnAndRow(1, $row)->getValue();
    unset($subdivisions[$row - 2]);
}
function process_is_sheet(PHPExcel_Worksheet $sheet) {
    global $information_systems;
    for ($row = 2; ($information_systems[$row - 2]['code'] = $sheet->getCellByColumnAndRow(0, $row)->getValue()) != ''; $row++)
        $information_systems[$row - 2]['title'] = $sheet->getCellByColumnAndRow(1, $row)->getValue();
    unset($information_systems[$row - 2]);
}
function process_support_measures(PHPExcel_Worksheet $sheet) {
    global $support;
    global $directions;
    $row = 2;
    $last_direction_id = 0;

    while (true) {
        $code = $sheet->getCellByColumnAndRow(0, $row)->getValue();
        if ($code == '') {
            if ($sheet->getCellByColumnAndRow(0, $row + 1)->getValue() != '') { //Aprašoma nauja kryptis
                $direction = $sheet->getCellByColumnAndRow(1, $row)->getValue();
                if (!preg_match("/(\d+)\sKRYPTIS\s(.+)/", $direction, $matches)) {
                    add_warning('Klaida krypties eilutės struktūroje. Lentelė: ' . $sheet->getTitle() . " eilutė: " . $row . " Eilutė praleista.");
                    continue;
                }
                array_push($directions, Array('id' => $matches[1], 'title' => $matches[2]));
                $last_direction_id = $matches[1];
            }
            else
                break; //Pasibaigė duomenys
        }
        else {
            $support[$row - 2]['code'] = $code;
            if (!preg_match("/(\d+)\.\s(.+)/", $sheet->getCellByColumnAndRow(1, $row)->getValue(), $matches)) {
                add_warning('Klaida paramos priemonės eilutės struktūroje. Lentelė: ' . $sheet->getTitle() . " eilutė: " . $row . " Eilutė praleista.");
                continue;
            }
            $support[$row - 2]['title'] = $matches[2];
            $support[$row - 2]['direction_id'] = $last_direction_id;
        }
        $row++;
    }
}
function process_is__subdivisions(PHPExcel_Worksheet $sheet) {
    global $is__subdivisions;
    global $information_systems;
    global $subdivisions;

    //TODO: remiamasi jau surinktais duomenim apie is ir padalinius, todėl neveiks jei IS-Padaliniai kortelė bus pvz pirmoji
    for ($row = 4; $row < 4 + count($information_systems); $row++)
        for ($column = 1; $column < 1 + count($subdivisions); $column++) {
            $value = $sheet->getCellByColumnAndRow($column, $row)->getValue();
            if ($value != '')
                array_push($is__subdivisions, Array(
                    'is_code' => $sheet->getCellByColumnAndRow(0, $row)->getValue(),
                    'subdivision_code' => $sheet->getCellByColumnAndRow($column, 3)->getValue()
                ));
        }
}
function process_support_administration(PHPExcel_Worksheet $sheet) {
    global $support_administration;
    $subdivisions_codes = Array();
    $support_measures_codes = Array();

    for ($column = 1; ($newValue = $sheet->getCellByColumnAndRow($column, 3)->getValue()) != ''; $column++) {
        if ($newValue != 'Iš viso laiko') {
            array_push($subdivisions_codes, $newValue);
        }
    }
    for ($row = 4; ($newValue = $sheet->getCellByColumnAndRow(0, $row)->getValue()) != ''; $row++) {
        array_push($support_measures_codes, $newValue);
    }
    for ($row = 4; $row < 4 + count($support_measures_codes); $row++)
        for ($column = 1; $column < 1 + count($subdivisions_codes); $column++) {
            $time = $sheet->getCellByColumnAndRow($column, $row)->getValue();
            if ($time != '')
                array_push($support_administration, Array(
                    'subdivision_code' => $subdivisions_codes[$column - 1],
                    'measure_code' => $support_measures_codes[$row - 4],
                    'time' => $time
                ));
        }
}
function defined_measure($measure_code){
    $sql_result = DB::query("SELECT COUNT(*) FROM support WHERE code='".$measure_code."'");
    $result = DB::row($sql_result);
    if($result[0] == 1){
        return true;
    }
    return false;
}

function process_support_quantities(PHPExcel_Worksheet $sheet) {
    global $historical_quantities;
    global $warning_about_dates;
    $entry = 0;
    for ($row = 2; ($measure_code = $sheet->getCellByColumnAndRow(0, $row)->getValue()) != ''; $row++) {
        $measure_code = strtolower($measure_code);
        if(!defined_measure($measure_code)){
            add_warning("Rastas dar neaprašytos paramos priemonės kiekis. Eilutė bus praleista. (Paramos priemonės kodas \"".$measure_code."\", ".$row." eilutė, faile \"".$_FILES['dataFile']['name']."\")");
            unset($historical_quantities[$entry]);
            continue;
        }
        $historical_quantities[$entry]['measure_code'] = $measure_code;
        $dateInExcelFormat = $sheet->getCellByColumnAndRow(1, $row)->getValue();
        $historical_quantities[$entry]['date'] = substr(PHPExcel_Style_NumberFormat::toFormattedString($dateInExcelFormat, "yyyy-mm-dd"), 0, 8)."01";
        $historical_quantities[$entry]['dateFrom'] = $historical_quantities[$entry]['date'];
        $historical_quantities[$entry]['dateTo'] = PHPExcel_Style_NumberFormat::toFormattedString($sheet->getCellByColumnAndRow(2, $row)->getValue(), "yyyy-mm-dd");
        if(strtotime($historical_quantities[$entry]['dateFrom']) > strtotime($historical_quantities[$entry]['dateTo'])){
            add_warning("Rastas nekorektiškas periodas. Nurodytas periodas baigiasi anksčiau nei prasideda. 
                Eilutė bus praleista. (Periodas nuo: ".$historical_quantities[$entry]['dateFrom']." iki: ".
                $historical_quantities[$entry]['dateTo'].", ".$row." eilutė, faile \"".$_FILES['dataFile']['name']."\").");
            unset($historical_quantities[$entry]);
            continue;
        }
        if(strtotime($historical_quantities[$entry]['dateFrom']) > time()){
            add_warning("Rastas paramos priemonės kiekis ateičiai. 
                Eilutė bus praleista. (Periodas nuo: ".$historical_quantities[$entry]['dateFrom']." iki: ".
                $historical_quantities[$entry]['dateTo'].", ".$row." eilutė, faile \"".$_FILES['dataFile']['name']."\").");
            unset($historical_quantities[$entry]);
            continue;
        }
        if(substr($historical_quantities[$entry]['dateFrom'], 0, 8) != substr($historical_quantities[$entry]['dateTo'], 0, 8)){
            add_warning("Rastas nekorektiškas periodas. Periodas turi prasidėti ir baigtis tą patį mėnesį. 
                Eilutė bus praleista. (Periodas nuo: ".$historical_quantities[$entry]['dateFrom']." iki: ".
                $historical_quantities[$entry]['dateTo'].", ".$row." eilutė, faile \"".$_FILES['dataFile']['name']."\").");
            unset($historical_quantities[$entry]);
            continue;
        }
        $historical_quantities[$entry]['row'] = $row;
        $historical_quantities[$entry]['administrated'] = $sheet->getCellByColumnAndRow(3, $row)->getValue();
        if((int)$historical_quantities[$entry]['administrated'] != $historical_quantities[$entry]['administrated'] && (int)$historical_quantities[$entry]['administrated'] < 0){
            add_warning("Rastas nekorektiškas paramos priemonės kiekis. Kiekis turi būti sveikas taigiamas skaičius arba 0. Eilutė bus praleista. (Paramos priemonės kiekis \"".$historical_quantities[$entry]['administrated']."\", ".$row." eilutė, faile \"".$_FILES['dataFile']['name']."\")");
            unset($historical_quantities[$entry]);
            continue;
        }
        /* Susumuojam priemonių kiekius kiekvienam mėnesiui */
        $already_entered = already_entered($historical_quantities, $historical_quantities[$entry]);
        if($already_entered !== false){
            $historical_quantities[$already_entered]['administrated'] += $historical_quantities[$entry]['administrated'];
            if($warning_about_dates == false){
                add_warning("Faile \"".$_FILES['dataFile']['name']."\" aprašyti 2 ar daugiau suadministruotų".
                    " paraiškų kiekiai tam pačiam mėnesiui. Šie kiekiai bus susumuoti.");
                $warning_about_dates = true; 
            }
            unset($historical_quantities[$entry]);
            continue;
        } 
        $entry++;
    }
}


function save_is_to_db(){
    global $information_systems;
    $sql_query = "INSERT INTO information_systems (code, title) VALUES";
    foreach ($information_systems as $is) {
        $sql_query .= sprintf(
                " ('%s', '%s'),", 
                mysql_real_escape_string($is['code']), 
                mysql_real_escape_string($is['title'])
        );
    }
    $sql_query = substr($sql_query, 0, strlen($sql_query)-1);
    $sql_query .= " ON DUPLICATE KEY UPDATE title=values(title)";
    if (($result = DB::query($sql_query)) === false) {
        add_warning('Klaida įrašant informacines sistemas į duomenų bazę (' . mysql_error() . '). Duomenys apie informacines sistemas neįrašyti.');
    }
}
function save_subdivisions_to_db(){
    global $subdivisions;
    $sql_query = "INSERT INTO subdivisions (code, title) VALUES";
    foreach ($subdivisions as $subdivision) {
        $sql_query .= sprintf(
                " ('%s', '%s'),", 
                mysql_real_escape_string($subdivision['code']), 
                mysql_real_escape_string($subdivision['title'])
        );
    }
    $sql_query = substr($sql_query, 0, strlen($sql_query)-1);
    $sql_query .= " ON DUPLICATE KEY UPDATE title=values(title)";
    if (($result = DB::query($sql_query)) === false) {
        add_warning('Klaida įrašant padalinius į duomenų bazę: ' . mysql_error() . '). Duomenys apie padalinius neįrašyti.');
    }
}
function save_support_to_db(){
    global $support;
    $sql_query = "INSERT INTO support(code, title, direction_id) VALUES";
    foreach ($support as $measure) {
        $sql_query .= sprintf(
                " ('%s', '%s', '%s'),", 
                mysql_real_escape_string($measure['code']), 
                mysql_real_escape_string($measure['title']), 
                mysql_real_escape_string($measure['direction_id'])
        );
    }
    $sql_query = substr($sql_query, 0, strlen($sql_query)-1);
    $sql_query .= " ON DUPLICATE KEY UPDATE title=values(title), direction_id=values(direction_id)";
    if (($result = DB::query($sql_query)) === false) {
        add_warning('Klaida įrašant paramos priemones į duomenų bazę: ' . mysql_error() . '). Duomenys apie paramos priemones neįrašyti.');
    }
}
function save_is__subdivisions_to_db(){
    global $is__subdivisions;
    $sql_query = "INSERT INTO is_subdivisions (is_code, subdivision_code, date) VALUES";
    foreach ($is__subdivisions as $use) {
        $sql_query .= sprintf(
                " ('%s', '%s', '%s'),", 
                mysql_real_escape_string($use['is_code']), 
                mysql_real_escape_string($use['subdivision_code']),
                date("Y-m-d", time())
        );
    }
    $sql_query = substr($sql_query, 0, strlen($sql_query)-1);
    $sql_query .= " ON DUPLICATE KEY UPDATE is_code=values(is_code), subdivision_code=values(subdivision_code)";
    if (($result = DB::query($sql_query)) === false) {
        add_warning('Klaida įrašant padalinių ir IS sąryšius į duomenų bazę: ' . mysql_error() . '). Duomenys apie padalinių ir is sąryšius neįrašyti.');
    }
}
function save_directions_to_db(){
    global $directions;
    $sql_query = "INSERT INTO directions (id, title) VALUES";
    foreach ($directions as $direction) {
        $sql_query .= sprintf(
                " ('%s', '%s'),", 
                mysql_real_escape_string($direction['id']), 
                mysql_real_escape_string($direction['title'])
        );
    }
    $sql_query = substr($sql_query, 0, strlen($sql_query)-1);
    $sql_query .= " ON DUPLICATE KEY UPDATE title=values(title)";
    if (($result = DB::query($sql_query)) === false) {
        add_warning('Klaida įrašant paramos priemonių kryptis į duomenų bazę: ' . mysql_error() . '). Duomenys apie paramos priemonių kryptis neįrašyti.');
    }
}
function save_support_administration_to_db() {
    global $support_administration;
    $sql_query = "INSERT INTO support_administration (measure_code, subdivision_code, time, date) VALUES";
    foreach ($support_administration as $rec) {
        $sql_query .= sprintf(
                //FIXME: pakeisti 3 argumentą į realaus skaičiaus, kai netas pradės veikt
                " ('%s', '%s', %s, '%s'),", 
                mysql_real_escape_string($rec['measure_code']), 
                mysql_real_escape_string($rec['subdivision_code']), 
                mysql_real_escape_string($rec['time']),
                date("Y-m-d", time())
        );
    }
    $sql_query = substr($sql_query, 0, strlen($sql_query)-1);
    $sql_query .= " ON DUPLICATE KEY UPDATE time=values(time)";
    if (($result = DB::query($sql_query)) === false) {
        add_warning('Klaida įrašant paramos administravimą į duomenų bazę: ' . mysql_error() . '). Duomenys apie paramos administravimą neįrašyti.');
    }
}
function save_quantities_to_db() {
    global $historical_quantities;
    $sql_query = "INSERT INTO historical_quantities (date, measure_code, administrated) VALUES";
    foreach ($historical_quantities as $record) {        
        $sql_query .= sprintf(
                " ('%s', '%s', %d),", 
                mysql_real_escape_string($record['date']), 
                mysql_real_escape_string($record['measure_code']),
                mysql_real_escape_string($record['administrated'])
        );
    }
    $sql_query = substr($sql_query, 0, strlen($sql_query)-1);
    $sql_query .= " ON DUPLICATE KEY UPDATE administrated=values(administrated)";
    if (($result = DB::query($sql_query)) === false) {
        add_warning('Klaida įrašant istorinius paraiškų kiekius į duomenų bazę: ' . mysql_error() . '). Duomenys apie istorinius paraiškų kiekius neįrašyti.');
    }
}

?>
